use sqlx::{query_as, PgExecutor};

use crate::{model, utils, Error, Result};

pub async fn summary<'a>(e: impl PgExecutor<'a>) -> Result<Vec<model::aggregation::Summary>> {
    let (start, end) = utils::dt::today_range()?;
    query_as(
        r#"SELECT 'user' AS title, count(*) AS total FROM users
UNION ALL
SELECT 'user-today' AS title, count(*) AS total FROM users WHERE dateline BETWEEN $1 AND $2
UNION ALL
SELECT 'url' AS title, COUNT(*) AS total FROM urls 
UNION ALL
SELECT 'url-today' AS title, COUNT(*) AS total FROM urls WHERE dateline BETWEEN $1 AND $2
UNION ALL
SELECT 'visit' AS title, COUNT(*) AS total FROM url_logs 
UNION ALL
SELECT 'visit-tody' AS title, COUNT(*) AS total FROM url_logs WHERE dateline BETWEEN $1 AND $2"#,
    )
    .bind(&start)
    .bind(&end)
    .fetch_all(e)
    .await
    .map_err(Error::from)
}

#[cfg(test)]
mod test {
    use sqlx::{postgres::PgPoolOptions, PgPool, Result};

    async fn get_pool() -> Result<PgPool> {
        let dsn = std::env::var("SHORT_URL_DB")
            .unwrap_or("postgres://short_url:short_url@127.0.0.1:5432/short_url".into());
        PgPoolOptions::new().max_connections(1).connect(&dsn).await
    }

    #[tokio::test]
    async fn test_agg_summary() {
        let pool = get_pool().await.unwrap();
        let s = super::summary(&pool).await.unwrap();
        assert!(s.len() == 6);
        println!("{:?}", s);
    }
}
